package net.cyue.web.easyquery.core.db.util;

import net.cyue.web.easyquery.core.db.SQLType;

import java.util.Arrays;
import java.util.HashSet;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * SQL类型检测工具类
 */
public class SQLTypeDetector {

    // 快速判断的查询关键字
    private static final Set<String> FAST_QUERY_KEYWORDS = new HashSet<>(Arrays.asList(
        "SELECT", "DESCRIBE", "EXPLAIN", "SHOW"
    ));

    // 快速判断的更新关键字
    private static final Set<String> FAST_UPDATE_KEYWORDS = new HashSet<>(Arrays.asList(
        "INSERT", "UPDATE", "DELETE", "MERGE", "REPLACE",
        "CREATE", "ALTER", "DROP", "TRUNCATE", "COMMIT", "ROLLBACK"
    ));

    // 复杂场景的查询关键字模式
    private static final Pattern COMPLEX_QUERY_PATTERN = Pattern.compile(
        "\\b(WITH)\\b.*?(SELECT|DESCRIBE|EXPLAIN|SHOW)\\b",
        Pattern.CASE_INSENSITIVE | Pattern.DOTALL
    );

    // 复杂场景的更新关键字模式
    private static final Pattern COMPLEX_UPDATE_PATTERN = Pattern.compile(
    "\\b(WITH)\\b.*?(INSERT|UPDATE|DELETE|MERGE|REPLACE|" +
            "CREATE|ALTER|DROP|TRUNCATE|COMMIT|ROLLBACK)\\b",
        Pattern.CASE_INSENSITIVE | Pattern.DOTALL
    );

    // CALL语句模式 - 存储过程可能包含更新操作，默认视为UPDATE
    private static final Pattern CALL_STATEMENT_PATTERN = Pattern.compile(
        "\\b(CALL)\\b",
        Pattern.CASE_INSENSITIVE
    );

    // 用于处理多行/* */注释的模式
    private static final Pattern MULTILINE_COMMENT_PATTERN = Pattern.compile("/\\*.*?\\*/", Pattern.DOTALL);

    // 用于处理--注释的模式
    private static final Pattern LINE_COMMENT_PATTERN = Pattern.compile("--.*$", Pattern.MULTILINE);

    // 用于处理WITH子句的模式
    private static final Pattern DML_PATTERN = Pattern.compile(
        "\\b(INSERT|UPDATE|DELETE|MERGE|REPLACE)\\b",
        Pattern.CASE_INSENSITIVE
    );

    /**
     * 判断SQL语句类型，自动选择快速或复杂判断模式
     * @param sql 要判断的SQL语句
     * @return 返回对应的SqlType枚举值
     */
    public static SQLType determineSqlType(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return SQLType.UNKNOWN;
        }

        // 先尝试快速判断
        SQLType fastResult = determineFastSqlType(sql);
        if (fastResult != SQLType.UNKNOWN) {
            return fastResult;
        }

        // 快速判断失败，使用复杂判断
        return determineComplexSqlType(sql);
    }

    /**
     * 快速判断SQL类型，仅基于第一个关键字
     * 性能优异，适用于大多数简单SQL语句
     */
    public static SQLType determineFastSqlType(String sql) {
        String processedSql = preprocessSqlForFastCheck(sql);
        String firstKeyword = getFirstKeyword(processedSql);

        if (firstKeyword == null) {
            return SQLType.UNKNOWN;
        }

        if (FAST_QUERY_KEYWORDS.contains(firstKeyword)) {
            return SQLType.QUERY;
        } else if (FAST_UPDATE_KEYWORDS.contains(firstKeyword)) {
            return SQLType.UPDATE;
        } else {
            return SQLType.UNKNOWN;
        }
    }

    /**
     * 复杂SQL判断，处理包含CTE、存储过程调用等复杂场景
     * 性能开销较大，但能处理更复杂的语句
     */
    public static SQLType determineComplexSqlType(String sql) {
        String processedSql = preprocessSqlForComplexCheck(sql);

        // 优先检查CALL语句（存储过程默认视为UPDATE类型）
        Matcher callMatcher = CALL_STATEMENT_PATTERN.matcher(processedSql);
        if (callMatcher.find()) {
            return SQLType.UPDATE;
        }

        // 先检查是否为复杂更新（调整优先级，确保UPDATE优先于QUERY判断）
        Matcher updateMatcher = COMPLEX_UPDATE_PATTERN.matcher(processedSql);
        if (updateMatcher.find()) {
            return SQLType.UPDATE;
        }

        // 再检查是否为复杂查询
        Matcher queryMatcher = COMPLEX_QUERY_PATTERN.matcher(processedSql);
        if (queryMatcher.find()) {
            return SQLType.QUERY;
        }

        // 特殊处理仅包含WITH的语句
        if (processedSql.trim().toUpperCase().startsWith("WITH")) {
            // 检查WITH后面是否跟有DML操作
            Matcher dmlMatcher = DML_PATTERN.matcher(processedSql);
            if (dmlMatcher.find()) {
                return SQLType.UPDATE;
            } else {
                return SQLType.QUERY;
            }
        }

        return SQLType.UNKNOWN;
    }

    /**
     * 快速检查的SQL预处理（轻量处理）
     */
    private static String preprocessSqlForFastCheck(String sql) {
        if (sql == null) {
            return "";
        }

        // 仅去除注释和前导空格，保持处理轻量
        String processed = sql;
        // 移除/* */格式的注释（使用预编译的模式处理多行注释）
        processed = MULTILINE_COMMENT_PATTERN.matcher(processed).replaceAll(" ");
        // 移除--格式的注释（使用预编译的模式处理多行）
        processed = LINE_COMMENT_PATTERN.matcher(processed).replaceAll(" ");
        // 处理完注释后再trim，确保能正确提取第一个关键字
        return processed.trim().toUpperCase();
    }

    /**
     * 复杂检查的SQL预处理（更彻底的处理）
     */
    private static String preprocessSqlForComplexCheck(String sql) {
        String processed = preprocessSqlForFastCheck(sql);
        // 移除字符串常量（处理包含关键字的字符串）
        processed = processed.replaceAll("'.*?'", "''");
        processed = processed.replaceAll("\".*?\"", "\"\"");
        // 移除多余空格
        return processed.replaceAll("\\s+", " ").trim();
    }

    /**
     * 获取SQL语句的第一个关键字
     */
    private static String getFirstKeyword(String processedSql) {
        if (processedSql.isEmpty()) {
            return null;
        }

        // 以空白字符分割，取第一个非空元素
        String[] tokens = processedSql.split("\\s+");
        return tokens.length > 0 ? tokens[0] : null;
    }

    // 测试方法
    public static void main(String[] args) {
        String[] testSqls = {
            // 简单语句
            "SELECT * FROM users",
            "UPDATE users SET name = 'test' WHERE id = 1",

            // 带注释的语句
            "  -- 这是一条查询语句\n  SELECT count(*) FROM orders",
            "/* 这是注释 */ DELETE FROM temp_data",
            "/* 多行\n注释 */ SELECT * FROM products",

            // 复杂语句
            "WITH cte AS (SELECT id FROM logs) INSERT INTO audit SELECT * FROM cte",
            "WITH temp AS (SELECT MAX(id) as max_id FROM users) UPDATE settings SET last_id = (SELECT max_id FROM temp)",
            "CALL process_data(); -- 存储过程可能包含更新操作",
            "WITH report_data AS (SELECT * FROM sales) SELECT * FROM report_data",

            // 包含字符串的语句
            "SELECT 'UPDATE' as action FROM system_logs",
            "INSERT INTO queries (sql) VALUES ('SELECT * FROM sensitive_data')",

            // 特殊情况
            "   ",  // 空语句
            "INVALID SQL STATEMENT"  // 无效语句
        };

        for (String sql : testSqls) {
            System.out.printf("SQL: %s%n", sql);
            System.out.printf("快速判断结果: %s%n", determineFastSqlType(sql));
            System.out.printf("最终判断结果: %s%n%n", determineSqlType(sql));
        }
    }
}
